import os
import sqlite3
from queue import Empty, Queue
from typing import Any, Dict, List, Optional

from .load_json import load_json


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


class DatabaseOperator:
    def __init__(self, pool_size: int = 10):
        # initial database
        project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        schema_path = os.path.join(project_path, "config", "schema.json")
        schema = load_json(schema_path)
        db_path = os.path.join(project_path, schema["database"]["path"])

        self.pool_size = pool_size
        self.connection_pool = Queue(maxsize=self.pool_size)

        # 初始化连接池
        for _ in range(self.pool_size):
            conn = sqlite3.connect(db_path, check_same_thread=False)
            conn.row_factory = dict_factory
            self.connection_pool.put(conn)

        self._is_connected = True
        self.init_database(schema["tables"])

    def _get_connection(self):
        """从连接池中获取一个连接"""
        try:
            return self.connection_pool.get(timeout=5)
        except Empty:
            # 如果连接池为空，创建新连接（作为后备方案）
            conn = sqlite3.connect(self.db_path, check_same_thread=False)
            conn.row_factory = dict_factory
            return conn

    def _return_connection(self, conn):
        """将连接归还到连接池"""
        try:
            self.connection_pool.put_nowait(conn)
        except:
            # 如果连接池已满，关闭连接
            conn.close()

    def _build_create_table_sql(self, table: Dict[str, Any]) -> str:
        """Build CREATE TABLE SQL statement from table schema"""
        columns = []
        foreigns = []
        for column in table["columns"]:
            col_def = f"{column['name']} {column['type']}"
            if column.get("primary_key"):
                col_def += " PRIMARY KEY"
            if column.get("autoincrement"):
                col_def += " AUTOINCREMENT"
            if column.get("nullable") is False:
                col_def += " NOT NULL"
            if "default" in column:
                if column["type"].upper() == "TIMESTAMP" and column["default"] == "CURRENT_TIMESTAMP":
                    col_def += f" DEFAULT (datetime('now', 'localtime'))"
                else:
                    col_def += f" DEFAULT {column['default']}"
            if column.get("foreign"):
                foreign_def = f"FOREIGN KEY ({column['name']}) REFERENCES {column['foreign']['table']} ({column['foreign']['to']})"
                if column["foreign"].get("on_delete"):
                    foreign_def += f" ON DELETE {column['foreign']['on_delete']}"
                foreigns.append(foreign_def)
            columns.append(col_def)

        sql = f"CREATE TABLE IF NOT EXISTS {table['name']} (\n"
        sql += ",\n".join(columns)
        if foreigns:
            sql += ",\n"
            sql += ",\n".join(foreigns)
        sql += "\n)"
        return sql

    def _check_table_exists(self, table_name: str) -> bool:
        """
        Check table exists
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            exist = True
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
            if cursor.fetchone() is None:
                exist = False

            return exist
        finally:
            self._return_connection(conn)

    def _update_table_schema(self, table_name: str, table_obj: Dict[str, Any]):
        """
        Update table schema to match provided table_obj definition.
        - Add missing columns
        - Drop extra columns not present in schema by rebuilding the table
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            # Read existing columns
            cursor.execute(f"PRAGMA table_info({table_name})")
            existing_columns_info = cursor.fetchall()
            existing_columns = {row["name"]: row for row in existing_columns_info}

            desired_columns = [col["name"] for col in table_obj.get("columns", [])]
            desired_columns_set = set(desired_columns)

            # 1) Add new columns that do not exist
            for item in table_obj.get("columns", []):
                column_name = item["name"]
                if column_name not in existing_columns:
                    sql = f"ALTER TABLE {table_name} ADD COLUMN {column_name} {item['type']}"
                    # Only attach DEFAULT when provided
                    if "default" in item:
                        if item["type"].upper() == "TIMESTAMP" and item["default"] == "CURRENT_TIMESTAMP":
                            sql += " DEFAULT (datetime('now', 'localtime'))"
                        else:
                            sql += f" DEFAULT {item['default']}"
                    # Avoid adding NOT NULL on ALTER TABLE to prevent failures
                    cursor.execute(sql)
                    conn.commit()

            # 2) Determine if we need to drop columns (columns not in schema)
            extra_columns = [name for name in existing_columns.keys() if name not in desired_columns_set]
            if not extra_columns:
                return

            # Rebuild the table to drop columns
            # - Create temp table with desired schema
            # - Copy intersecting columns
            # - Drop old table and rename temp

            cursor.execute("PRAGMA foreign_keys=OFF")
            conn.commit()

            # Create temp table definition
            temp_table_name = f"{table_name}__tmp"

            # Ensure temp table does not exist
            cursor.execute(f"DROP TABLE IF EXISTS {temp_table_name}")

            # Build CREATE TABLE for temp table
            temp_table_obj = dict(table_obj)
            temp_table_obj["name"] = temp_table_name
            create_sql = self._build_create_table_sql(temp_table_obj)
            cursor.execute(create_sql)

            # Determine intersection columns to preserve data
            intersection_columns = [name for name in desired_columns if name in existing_columns]
            if intersection_columns:
                cols_csv = ", ".join(intersection_columns)
                insert_sql = f"INSERT INTO {temp_table_name} ({cols_csv}) " f"SELECT {cols_csv} FROM {table_name}"
                cursor.execute(insert_sql)

            # Replace original table
            cursor.execute(f"DROP TABLE {table_name}")
            cursor.execute(f"ALTER TABLE {temp_table_name} RENAME TO {table_name}")

            # Re-create indexes
            for index_key in table_obj.get("indexes", []):
                cursor.execute(f"CREATE INDEX {table_name}_{index_key}_index ON {table_name} ({index_key});")

            conn.commit()
        finally:
            # Always re-enable foreign keys
            cursor.execute("PRAGMA foreign_keys=ON")
            conn.commit()
            self._return_connection(conn)

    def init_database(self, tables: list, force: bool = False) -> None:
        """
        initial database tables
        Args:
            tables: List of dictionaries containing table definitions
            force: If True, will drop existing tables before creating new ones
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            for table_obj in tables:
                table_name = table_obj["name"]
                if force:
                    # Drop existing table
                    cursor.execute(f"DROP TABLE IF EXISTS {table_name};")
                    create_sql = self._build_create_table_sql(table_obj)
                    cursor.execute(create_sql)

                    # Create indexs
                    for index_key in table_obj["indexes"]:
                        cursor.execute(
                            f"CREATE INDEX {table_obj['name']}_{index_key}_index ON {table_obj['name']} ({index_key});"
                        )
                elif self._check_table_exists(table_name):
                    # update table schema (add missing columns and drop extras)
                    self._update_table_schema(table_name, table_obj)
                else:
                    # not exists
                    create_sql = self._build_create_table_sql(table_obj)
                    cursor.execute(create_sql)

                    # Create indexs
                    for index_key in table_obj["indexes"]:
                        cursor.execute(
                            f"CREATE INDEX {table_obj['name']}_{index_key}_index ON {table_obj['name']} ({index_key});"
                        )
                conn.commit()

        except Exception as e:
            raise ValueError(e)
        finally:
            self._return_connection(conn)

    def insert_one(self, table_name: str, data: Dict[str, Any]) -> None:
        """
        Insert a new record into the specified table
        Args:
            table_name: Name of the table to insert into
            data: Dictionary containing column names and values
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            columns = ", ".join(data.keys())
            placeholders = ", ".join(["?"] * len(data))
            sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
            cursor.execute(sql, tuple(data.values()))
            conn.commit()
            return cursor.lastrowid
        except Exception as e:
            conn.rollback()
            raise ValueError(e)
        finally:
            self._return_connection(conn)

    def insert_many(self, table_name: str, data_list: List[Dict[str, Any]]) -> None:
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            if not data_list:
                return True

            # Use the first record to get column names
            columns = list(data_list[0].keys())
            placeholders = ", ".join(["?"] * len(columns))
            sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"

            # Convert list of dicts to list of tuples
            values = [tuple(record.values()) for record in data_list]

            # Execute many
            cursor.executemany(sql, values)
            conn.commit()

        except Exception as e:
            conn.rollback()
        finally:
            self._return_connection(conn)

    def get_one(self, table_name: str, conditions: Optional[Dict[str, Any]] = None) -> Optional[Dict[str, Any]]:
        """
        Get a single record from the specified table
        Args:
            table_name: Name of the table to query
            conditions: Dictionary containing column names and values for filtering
        Returns:
            A dictionary representing the record, or None if not found
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            sql = f"SELECT * FROM {table_name}"
            if conditions:
                where_clause = " AND ".join([f"{k} = ?" for k in conditions.keys()])
                sql += f" WHERE {where_clause}"
                cursor.execute(sql, tuple(conditions.values()))
            else:
                cursor.execute(sql)

            return cursor.fetchone()
        finally:
            self._return_connection(conn)

    def get_all(
        self,
        table_name: str,
        conditions: Optional[Dict[str, Any]] = None,
        order_by: Optional[str] = None,
        limit: int = None,
    ) -> List[Dict[str, Any]]:
        """
        Get all records from the specified table
        Args:
            table_name: Name of the table to query
            conditions: Dictionary containing column names and values for filtering
            order_by: Optional string for SQL ORDER BY clause, e.g., "id DESC"
        Returns:
            A list of dictionaries representing the records
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            sql = f"SELECT * FROM {table_name}"
            params = []
            if conditions:
                wheres = []
                for k, v in conditions.items():
                    if k.endswith("__ne"):
                        field = k[:-4]
                        wheres.append(f"{field} != ?")
                        params.append(v)
                    elif k.endswith("__lt"):
                        field = k[:-4]
                        wheres.append(f"{field} < ?")
                        params.append(v)
                    elif k.endswith("__gt"):
                        field = k[:-4]
                        wheres.append(f"{field} > ?")
                        params.append(v)
                    elif k.endswith("__contains"):
                        field = k[:-10]
                        wheres.append(f"{field} LIKE ?")
                        params.append(f"%{v}%")
                    elif isinstance(v, list):
                        if v:
                            wheres.append(f"{k} IN ({','.join(['?'] * len(v))})")
                            params.extend(v)
                    else:
                        wheres.append(f"{k} = ?")
                        params.append(v)

                sql += f" WHERE " + " AND ".join(wheres)
            if order_by:
                sql += f" ORDER BY {order_by}"
            if limit:
                sql += f" LIMIT ?"
                params.append(limit)
            cursor.execute(sql, tuple(params))
            return cursor.fetchall()
        finally:
            self._return_connection(conn)

    def delete_one(self, table_name: str, conditions: Optional[Dict[str, Any]] = None) -> bool:
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            sql = f"DELETE FROM {table_name}"
            if conditions:
                where_clause = " AND ".join([f"{k} = ?" for k in conditions.keys()])
                sql += f" WHERE {where_clause}"
                params = tuple(conditions.values())
            else:
                raise ValueError("Conditions must be provided for delete_many.")
            cursor.execute(sql, params)
            conn.commit()
            return True
        except Exception as e:
            conn.rollback()
            return False
        finally:
            self._return_connection(conn)

    def get_count(
        self,
        table_name: str,
        conditions: Optional[Dict[str, Any]] = None,
    ) -> int:
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            sql = f"SELECT COUNT(*) as count FROM {table_name}"
            params = []
            if conditions:
                wheres = []
                for k, v in conditions.items():
                    if k.endswith("__ne"):
                        field = k[:-4]
                        wheres.append(f"{field} != ?")
                        params.append(v)
                    elif k.endswith("__lt"):
                        field = k[:-4]
                        wheres.append(f"{field} < ?")
                        params.append(v)
                    elif k.endswith("__gt"):
                        field = k[:-4]
                        wheres.append(f"{field} > ?")
                        params.append(v)
                    elif k.endswith("__contains"):
                        field = k[:-10]
                        wheres.append(f"{field} LIKE ?")
                        params.append(f"%{v}%")
                    elif isinstance(v, list):
                        if v:
                            wheres.append(f"{k} IN ({','.join(['?'] * len(v))})")
                            params.extend(v)
                    else:
                        wheres.append(f"{k} = ?")
                        params.append(v)

                sql += f" WHERE " + " AND ".join(wheres)
                cursor.execute(sql, tuple(params))
            else:
                cursor.execute(sql)

            result = cursor.fetchone()
            return result["count"] if result else 0
        except Exception as e:
            return 0
        finally:
            self._return_connection(conn)

    def update_one(self, table_name: str, data: Dict[str, Any], identifier: Dict[str, Any]) -> bool:
        """
        Update a record in the specified table by id or name.
        Args:
            table_name: Name of the table to update
            data: Dictionary of columns and new values to set
            identifier: Dictionary with one key, e.g., {'id': 1} or {'name': 'xxx'}
        Returns:
            True if update succeeded, False otherwise
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            if not identifier or len(identifier) != 1:
                raise ValueError("Identifier must be a dict with a single key (id or name).")
            set_clause = ", ".join([f"{k} = ?" for k in data.keys()])
            id_key, id_value = list(identifier.items())[0]
            sql = f"UPDATE {table_name} SET {set_clause} WHERE {id_key} = ?"
            try:
                cursor.execute(sql, tuple(data.values()) + (id_value,))
                conn.commit()
                return cursor.rowcount > 0
            except Exception as e:
                conn.rollback()
                return False
        finally:
            self._return_connection(conn)

    def update_many(self, table_name: str, data: Dict[str, Any], conditions: Dict[str, Any]) -> int:
        """
        Update multiple records in the specified table based on conditions.
        Args:
            table_name: Name of the table to update
            data: Dictionary of columns and new values to set
            conditions: Dictionary containing column names and values for filtering
        Returns:
            Number of rows affected, or -1 if update failed
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            if not conditions:
                raise ValueError("Conditions must be provided for update_many.")

            set_clause = ", ".join([f"{k} = ?" for k in data.keys()])
            where_clause = " AND ".join([f"{k} = ?" for k in conditions.keys()])

            sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"

            # Combine data values and condition values
            params = tuple(data.values()) + tuple(conditions.values())

            cursor.execute(sql, params)
            conn.commit()

            return cursor.rowcount

        except Exception as e:
            conn.rollback()
            return -1
        finally:
            self._return_connection(conn)

    def upsert_one(self, table_name: str, data: Dict[str, Any], identifier: Dict[str, Any]) -> bool:
        """
        Update a record if it exists, otherwise insert a new one.
        Args:
            table_name: Name of the table
            data: Dictionary of columns and values to insert or update
            identifier: Dictionary with one key to uniquely identify the record (e.g., {'id': 1} or {'name': 'xxx'})
        Returns:
            True if update or insert succeeded, False otherwise
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            existing = self.get_one(table_name, identifier)
            if existing:
                # Update
                return self.update_one(table_name, data, identifier)
            else:
                # Insert (merge identifier and data)
                insert_data = data.copy()
                insert_data.update(identifier)
                self.insert_one(table_name, insert_data)
                return True
        except Exception as e:
            conn.rollback()
            return False
        finally:
            self._return_connection(conn)

    def execute_query(self, sql: str, data: tuple = None):
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            if data:
                cursor.execute(sql, data)
            else:
                cursor.execute(sql)
            return cursor.fetchall()
        except Exception as e:
            return False
        finally:
            self._return_connection(conn)

    def close(self) -> None:
        """
        Close the database connection
        """
        self._is_connected = False
        while not self.connection_pool.empty():
            try:
                conn = self.connection_pool.get_nowait()
                conn.close()
            except Empty:
                break
